package com.dushibao.dao;

import cn.hutool.core.collection.CollUtil;
import com.dushibao.entity.User;
import com.dushibao.util.DBUtils;
import com.dushibao.util.Page;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

public class UserDao {
    public List<User> queryByUserNameAndPassword(String userName,String password){
        try{

            String sql = "select id,addTime,userName,password from dushibao_user where userName=? and password =?";

            Connection conn = DBUtils.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setString(1,userName);
            stmt.setString(2,password);

            ResultSet rs = stmt.executeQuery();

            List<User> users = new ArrayList<>();
            while(rs.next()){
                User user = new User();
                user.setId(rs.getLong("id"));
                user.setAddTime(rs.getTimestamp("addTime").toLocalDateTime());
                user.setUserName(rs.getString("userName"));
                user.setPassword(rs.getString("password"));
                users.add(user);
            }
            return users;
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }


    public Page page(Page page, String userName) {

        try{
            StringBuilder querySql = new StringBuilder("select id,addTime,userName,password from dushibao_user where 1=1 ");
            if(userName != null && userName.length()>0){
                querySql.append(" and userName  like '%"+userName+"%'");
            }

            querySql.append(" limit ?,? ");

            Connection conn = DBUtils.getConnection();

            PreparedStatement stmt = conn.prepareStatement(querySql.toString());
            stmt.setInt(1,page.getStart());
            stmt.setInt(2,page.getPageSize());

            ResultSet rs = stmt.executeQuery();

            List<Object> users = new ArrayList<>();
            while(rs.next()){
                User user = new User();
                user.setId(rs.getLong("id"));
                user.setAddTime(rs.getTimestamp("addTime").toLocalDateTime());
                user.setUserName(rs.getString("userName"));
                user.setPassword(rs.getString("password"));
                users.add(user);
            }

            DBUtils.close(rs,stmt,conn);

            StringBuilder countSql = new StringBuilder("select count(*) as total from dushibao_user where 1=1 ");
            if(userName != null && userName.length()>0){
                countSql.append(" and userName  like '%"+userName+"%'");
            }

            conn = DBUtils.getConnection();
            stmt = conn.prepareStatement(countSql.toString());

            rs = stmt.executeQuery();

            int total = 0;
            if(rs.next()){
                total = rs.getInt("total");
            }

            Page iPage = new Page();
            iPage.setCurrent(page.getCurrent());
            iPage.setPageSize(page.getPageSize());
            iPage.setData(users);
            iPage.setPages(total % iPage.getPageSize()==0?total/ iPage.getPageSize():total/ iPage.getPageSize()+1);
            iPage.setTotal(total);

            return iPage;
        }catch (Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }



    public User getById(long id) {
        try{
            String sql = "select id,addTime,userName,password from dushibao_user where id = ?";
            Connection conn = DBUtils.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setLong(1,id);
            ResultSet rs = stmt.executeQuery();
            User user = new User();

            if(rs.next()){
                user.setId(rs.getLong("id"));
                user.setAddTime(rs.getTimestamp("addTime").toLocalDateTime());
                user.setUserName(rs.getString("userName"));
                user.setPassword(rs.getString("password"));
            }

            return user;
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }


    public int update(User user)  {
        try{
            String sql = "update dushibao_user set userName=?,password=?,res_id=? where id=?";
            Connection conn = DBUtils.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setString(1,user.getUserName());
            stmt.setString(2,user.getPassword());
            if(user.getResId()!=null) {
                stmt.setLong(3, user.getResId());
            }else {
                stmt.setNull(3, Types.BIGINT);
            }
            stmt.setLong(4,user.getId());
            return stmt.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }


    public int save(User user){

        try{
            String sql = "insert into dushibao_user(userName,password) values(?,?)";
            Connection conn = DBUtils.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setString(1,user.getUserName());
            stmt.setString(2,user.getPassword());
            return stmt.executeUpdate();
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }


    public List<User> queryByUserName(String userName){
        try{
            String sql = "select id,addTime,userName,password from dushibao_user where userName = ?";
            Connection conn = DBUtils.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setString(1,userName);
            ResultSet rs = stmt.executeQuery();
            List<User> users = new ArrayList<>();

            while(rs.next()){
                Long id = rs.getLong("id");
                LocalDateTime addTime = rs.getTimestamp("addTime").toLocalDateTime();
                String name = rs.getString("userName");
                String password = rs.getString("password");

                User user = new User();
                user.setId(id);
                user.setAddTime(addTime);
                user.setUserName(name);
                user.setPassword(password);
                users.add(user);
            }

            return users;
        }catch (Exception e) {
            throw new RuntimeException(e);
        }
    }


    public Integer deleteById(List<Long> ids) {
        try{

            StringBuilder sql = new StringBuilder(" delete from dushibao_user where id in ( ");
            for (int i = 0; i < ids.size(); i++) {
                sql.append(ids.get(i));

                if(i<ids.size()-1){
                    sql.append(",");
                }

            }
            sql.append(")");

            Connection conn = DBUtils.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            return stmt.executeUpdate();
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }

    public List<User> list(List<Long> userIds)  {
        try{
            StringBuilder sbd = new StringBuilder("select id,addTime,userName,password from dushibao_user where 1 = 1");

            if(CollUtil.isNotEmpty(userIds)){
                sbd.append(" and id in ( ");
                for (int i = 0; i < userIds.size(); i++) {
                    sbd.append(userIds.get(i));
                    if(i < userIds.size() - 1){
                        sbd.append(",");
                    }
                }
                sbd.append(" ) ");
            }

            Connection conn = DBUtils.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sbd.toString());

            ResultSet rs = stmt.executeQuery();

            List<User> users = new ArrayList<>();
            while(rs.next()){
                User user = new User();
                user.setId(rs.getLong("id"));
                user.setAddTime(rs.getTimestamp("addTime").toLocalDateTime());
                user.setUserName(rs.getString("userName"));
                user.setPassword(rs.getString("password"));
                users.add(user);
            }
            return users;
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }

    public boolean saveBatch(List<User> users) {

        try{
            String sql = "insert into dushibao_user(addTime,userName,password) values(?,?,?)";

            Connection conn = DBUtils.getConnection();
            conn.setAutoCommit(false);
            PreparedStatement stmt = conn.prepareStatement(sql);

            for (User user : users) {
                stmt.setObject(1,user.getAddTime());
                stmt.setString(2,user.getUserName());
                stmt.setString(3,user.getPassword());
                stmt.executeUpdate();
            }

            conn.commit();
            return true;
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }
}
